
CREATE TABLE  "AUTHORITY" 
   (	"AUTHORITYID" NUMBER(*,0) NOT NULL ENABLE, 
	"AUTHORITY" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_AUTHORITYID" PRIMARY KEY ("AUTHORITYID") ENABLE
   )
/
CREATE TABLE  "CLIENT" 
   (	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	"CLIENT_NAME"  "NAME" , 
	"PHONE" VARCHAR2(15), 
	"EMAIL" VARCHAR2(40), 
	 CONSTRAINT "PK_CLIENTID" PRIMARY KEY ("CLIENTID") ENABLE
   )
/
CREATE TABLE  "CONTRACT" 
   (	"CONTRACTID" NUMBER(*,0) NOT NULL ENABLE, 
	"CREATE_DATE" DATE DEFAULT sysdate, 
	"CONTRACT_NUMBER" NVARCHAR2(10) NOT NULL ENABLE, 
	"MONEY" NUMBER(10,2) NOT NULL ENABLE, 
	 CONSTRAINT "PK_CONTRACT" PRIMARY KEY ("CONTRACTID") ENABLE
   )
/
CREATE TABLE  "ORDERS" 
   (	"ORDERID" NUMBER(*,0) NOT NULL ENABLE, 
	"TYPE" NVARCHAR2(4) DEFAULT 'buy', 
	"CREATE_DATA" DATE, 
	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "CK_TYPE" CHECK ((type = 'buy') or (type = 'sale')) ENABLE, 
	 CONSTRAINT "PK_ORDER" PRIMARY KEY ("ORDERID") ENABLE, 
	 CONSTRAINT "FK_CLIENT" FOREIGN KEY ("CLIENTID")
	  REFERENCES  "CLIENT" ("CLIENTID") ENABLE
   )
/
CREATE TABLE  "CONTRACT_ORDER" 
   (	"BUY_ORDER_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"SALE_ORDER_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"CONTRACTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_CONTRACT_ORDER" PRIMARY KEY ("BUY_ORDER_ID", "SALE_ORDER_ID", "CONTRACTID") ENABLE, 
	 CONSTRAINT "FK_SALE_ORDER_ID" FOREIGN KEY ("SALE_ORDER_ID")
	  REFERENCES  "ORDERS" ("ORDERID") ENABLE, 
	 CONSTRAINT "FK_BUY_ORDER_ID" FOREIGN KEY ("BUY_ORDER_ID")
	  REFERENCES  "ORDERS" ("ORDERID") ENABLE
   )
/
CREATE TABLE  "DISTRICT" 
   (	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	"NAME" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_DISTRICT" PRIMARY KEY ("DISTRICTID") ENABLE
   )
/
CREATE TABLE  "POST" 
   (	"POSTID" NUMBER(*,0) NOT NULL ENABLE, 
	"NAME" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_POST" PRIMARY KEY ("POSTID") ENABLE
   )
/
CREATE TABLE  "EMPLOYEE" 
   (	"EMPLOYEEID" NUMBER(*,0) NOT NULL ENABLE, 
	"EMPLOYEE_NAME"  "NAME" , 
	"POSTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPLOYEEID") ENABLE, 
	 CONSTRAINT "FK_POST" FOREIGN KEY ("POSTID")
	  REFERENCES  "POST" ("POSTID") ENABLE
   )
/
CREATE TABLE  "EMPLOYEE_CONTRACT" 
   (	"EMPLOYEEID" NUMBER(*,0) NOT NULL ENABLE, 
	"CONTRACTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_EMPLOYEE_CONTRACT" PRIMARY KEY ("EMPLOYEEID", "CONTRACTID") ENABLE, 
	 CONSTRAINT "FK_EMPLOYEE_EMP" FOREIGN KEY ("EMPLOYEEID")
	  REFERENCES  "EMPLOYEE" ("EMPLOYEEID") ENABLE, 
	 CONSTRAINT "FK_CONTRACT_CON" FOREIGN KEY ("CONTRACTID")
	  REFERENCES  "CONTRACT" ("CONTRACTID") ENABLE
   )
/
CREATE TABLE  "FLAT" 
   (	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLOOR" NUMBER(3,0) NOT NULL ENABLE, 
	"FLOORS_COUNT" NUMBER(3,0), 
	"SQUARE" NUMBER(5,2) NOT NULL ENABLE, 
	"ROOMS" NUMBER(1,0) NOT NULL ENABLE, 
	"LIVING_SQUARE" NUMBER(5,2), 
	"COOK_SQUARE" NUMBER(5,2), 
	"ADDRESS"  "ADDRESS"  NOT NULL ENABLE, 
	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_FLAT" PRIMARY KEY ("FLATID") ENABLE, 
	 CONSTRAINT "FK_DISTRICTID_FLAT" FOREIGN KEY ("DISTRICTID")
	  REFERENCES  "DISTRICT" ("DISTRICTID") ENABLE
   )
/
CREATE TABLE  "FLATS_BUY" 
   (	
    "FLATSBUYID" NUMBER(*,0) NOT NULL ENABLE, 
	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLOOR" NUMBER(3,0), 
	"PRICE_FROM" NUMBER(10,2) NOT NULL ENABLE, 
	"PRICE_TO" NUMBER(10,2) NOT NULL ENABLE, 
	"SQUARE" NUMBER(5,2), 
	"PHONE" NVARCHAR2(15), 
	 CONSTRAINT "PK_FLATSBUYID" PRIMARY KEY ("FLATSBUYID") ENABLE, 
	 CONSTRAINT "FK_DISTRICT" FOREIGN KEY ("DISTRICTID")
	  REFERENCES  "DISTRICT" ("DISTRICTID") ENABLE
   )
/
CREATE TABLE  "SUITABLE_OPTIONS" 
   (	"OPTIONSID" NUMBER(*,0) NOT NULL ENABLE, 
	"ORDERID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_OPTIONSID" PRIMARY KEY ("OPTIONSID") ENABLE, 
	 CONSTRAINT "FK_ORDER_OPTIONS" FOREIGN KEY ("ORDERID")
	  REFERENCES  "ORDERS" ("ORDERID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "FK_FLAT_OPTIONS" FOREIGN KEY ("FLATID")
	  REFERENCES  "FLAT" ("FLATID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "MEETING" 
   (	"MEETINGID" NUMBER(*,0) NOT NULL ENABLE, 
	"ONDATE" TIMESTAMP (6), 
	"OPTIONSID" NUMBER(*,0) NOT NULL ENABLE, 
	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_MEETING" PRIMARY KEY ("MEETINGID") ENABLE, 
	 CONSTRAINT "FK_OPTIONS_MEETING" FOREIGN KEY ("OPTIONSID")
	  REFERENCES  "SUITABLE_OPTIONS" ("OPTIONSID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "FK_CLIENT_MEETING" FOREIGN KEY ("CLIENTID")
	  REFERENCES  "CLIENT" ("CLIENTID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "MONEY_FLOW" 
   (	"MONEY_FLOW_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"ONDATE" TIMESTAMP (6), 
	"MONEY" NUMBER(10,2), 
	"CONTRACTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_MONEYID" PRIMARY KEY ("MONEY_FLOW_ID") ENABLE, 
	 CONSTRAINT "FK_CONTRACT_MONEY_FLOW" FOREIGN KEY ("CONTRACTID")
	  REFERENCES  "CONTRACT" ("CONTRACTID") ENABLE
   )
/
CREATE TABLE  "SALE_FLAT" 
   (	"SALE_FLAT_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	"ORDERID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_SALE_FLAT_ID" PRIMARY KEY ("SALE_FLAT_ID") ENABLE, 
	 CONSTRAINT "FK_SALE_FLAT_FLAT" FOREIGN KEY ("FLATID")
	  REFERENCES  "FLAT" ("FLATID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "FK_ORDER_FLAT" FOREIGN KEY ("ORDERID")
	  REFERENCES  "ORDERS" ("ORDERID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "USERS" 
   (	"USERSID" NUMBER(*,0) NOT NULL ENABLE, 
	"USERNAME" NVARCHAR2(30), 
	"PASSWORD" VARCHAR2(32) NOT NULL ENABLE, 
	"ENABLED" NUMBER(1,0) DEFAULT 0, 
	 CONSTRAINT "CH_ENABLED" CHECK ((enabled = 0) or (enabled = 1)) ENABLE, 
	 CONSTRAINT "PK_USERSID" PRIMARY KEY ("USERSID") ENABLE
   )
/
CREATE TABLE  "USER_AUTHORITY" 
   (	"USERSID" NUMBER(*,0) NOT NULL ENABLE, 
	"AUTHORITYID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_USERAUTHORITY" PRIMARY KEY ("USERSID", "AUTHORITYID") ENABLE, 
	 CONSTRAINT "FK_USERS_AUTHORITY" FOREIGN KEY ("USERSID")
	  REFERENCES  "USERS" ("USERSID") ENABLE, 
	 CONSTRAINT "FK_AUTHORITY" FOREIGN KEY ("AUTHORITYID")
	  REFERENCES  "AUTHORITY" ("AUTHORITYID") ENABLE
   )
/



CREATE UNIQUE INDEX  "PK_AUTHORITYID" ON  "AUTHORITY" ("AUTHORITYID")
/
CREATE UNIQUE INDEX  "PK_CLIENTID" ON  "CLIENT" ("CLIENTID")
/
CREATE UNIQUE INDEX  "PK_CONTRACT" ON  "CONTRACT" ("CONTRACTID")
/
CREATE UNIQUE INDEX  "PK_CONTRACT_ORDER" ON  "CONTRACT_ORDER" ("BUY_ORDER_ID", "SALE_ORDER_ID", "CONTRACTID")
/
CREATE UNIQUE INDEX  "PK_DISTRICT" ON  "DISTRICT" ("DISTRICTID")
/
CREATE UNIQUE INDEX  "PK_EMPLOYEE" ON  "EMPLOYEE" ("EMPLOYEEID")
/
CREATE UNIQUE INDEX  "PK_EMPLOYEE_CONTRACT" ON  "EMPLOYEE_CONTRACT" ("EMPLOYEEID", "CONTRACTID")
/
CREATE UNIQUE INDEX  "PK_FLAT" ON  "FLAT" ("FLATID")
/
CREATE UNIQUE INDEX  "PK_FLATSBUYID" ON  "FLATS_BUY" ("FLATSBUYID")
/
CREATE UNIQUE INDEX  "PK_MEETING" ON  "MEETING" ("MEETINGID")
/
CREATE UNIQUE INDEX  "PK_MONEYID" ON  "MONEY_FLOW" ("MONEY_FLOW_ID")
/
CREATE UNIQUE INDEX  "PK_OPTIONSID" ON  "SUITABLE_OPTIONS" ("OPTIONSID")
/
CREATE UNIQUE INDEX  "PK_ORDER" ON  "ORDERS" ("ORDERID")
/
CREATE UNIQUE INDEX  "PK_POST" ON  "POST" ("POSTID")
/
CREATE UNIQUE INDEX  "PK_SALE_FLAT_ID" ON  "SALE_FLAT" ("SALE_FLAT_ID")
/
CREATE UNIQUE INDEX  "PK_USERAUTHORITY" ON  "USER_AUTHORITY" ("USERSID", "AUTHORITYID")
/
CREATE UNIQUE INDEX  "PK_USERSID" ON  "USERS" ("USERSID")
/


 CREATE SEQUENCE   "AUTHORITY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "CLIENT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "CONTRACT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "DISTRICT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "EMPLOYEE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "FLATS_BUY_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "FLAT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "MEETING_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "MONEY_FLOW_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "ORDERS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "POST_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "SALE_FLAT_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "SUITABLE_OPTOINS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "USERS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/



CREATE OR REPLACE TRIGGER  "AUTHORITY_INSERT_TRIGGER" 
before insert on authority for each row
begin
select authority_seq.nextval into :NEW.authorityid from dual;
end;
/
ALTER TRIGGER  "AUTHORITY_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "CLIENT_INSERT_TRIGGER" 
before insert on client for each row
begin
select client_seq.nextval into :NEW.clientid from dual;
end;
/
ALTER TRIGGER  "CLIENT_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "CONTRACT_INSERT_TRIGGER" 
before insert on contract for each row
begin
select contract_seq.nextval into :NEW.contractid from dual;
end;
/
ALTER TRIGGER  "CONTRACT_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "DISTRICT_INSERT_TRIGGER" 
before insert on district for each row
begin
select district_seq.nextval into :NEW.districtid from dual;
end;
/
ALTER TRIGGER  "DISTRICT_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "EMPLOYEE_INSERT_TRIGGER" 
before insert on employee for each row
begin
select employee_seq.nextval into :NEW.employeeid from dual;
end;
/
ALTER TRIGGER  "EMPLOYEE_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "FLATS_BUY_INSERT_TRIGGER" 
before insert on flats_buy for each row
begin
select flats_buy_seq.nextval into :NEW.flatsbuyid from dual;
end;
/
ALTER TRIGGER  "FLATS_BUY_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "FLAT_INSERT_TRIGGER" 
before insert on flat for each row
begin
select flat_seq.nextval into :NEW.flatid from dual;
end;
/
ALTER TRIGGER  "FLAT_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "MEETING_INSERT_TRIGGER" 
before insert on meeting for each row
begin
select meeting_seq.nextval into :NEW.meetingid from dual;
end;
/
ALTER TRIGGER  "MEETING_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "MONEY_FLOW_INSERT_TRIGGER" 
before insert on meeting for each row
begin
select money_flow_seq.nextval into :NEW.money_flow_id from dual;
end;
/
ALTER TRIGGER  "MONEY_FLOW_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "OPTIONS_INSERT_TRIGGER" 
before insert on suitable_options for each row
begin
select SUITABLE_OPTOINS_SEQ.nextval into :NEW.OPTIONSID from dual;
end;
/
ALTER TRIGGER  "OPTIONS_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "ORDERS_INSERT_TRIGGER" 
before insert on orders for each row
begin
select orders_seq.nextval into :NEW.order_id from dual;
end;
/
ALTER TRIGGER  "ORDERS_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "POST_INSERT_TRIGGER" 
before insert on post for each row
begin
select post_seq.nextval into :NEW.post_id from dual;
end;
/
ALTER TRIGGER  "POST_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "SALE_FLAT_INSERT_TRIGGER" 
before insert on sale_flat for each row
begin
select sale_flat_seq.nextval into :NEW.sale_flat_id from dual;
end;
/
ALTER TRIGGER  "SALE_FLAT_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "USERS_INSERT_TRIGGER" 
before insert on users for each row
begin
select users_SEQ.nextval into :NEW.users from dual;
end;
/
ALTER TRIGGER  "USERS_INSERT_TRIGGER" ENABLE


			
	

	

